import pymysql

class MySQLHelper:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None

    def connect(self):
        """连接数据库"""
        self.connection = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset,
            cursorclass=pymysql.cursors.DictCursor
        )

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

    def execute(self, sql, params=None):
        """执行SQL语句"""
        try:
            with self.connection.cursor() as cursor:
                if params:
                    cursor.execute(sql, params)
                else:
                    cursor.execute(sql)
                # 如果是查询操作，返回结果
                if sql.lower().startswith('select'):
                    return cursor.fetchall()
                # 否则提交事务
                self.connection.commit()
        except Exception as e:
            print(f"Error executing SQL: {e}")
            if self.connection:
                self.connection.rollback()
            return None

    def insert(self, table, data):
        """插入数据"""
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({values})"
        return self.execute(sql, tuple(data.values()))

    def update(self, table, data, where):
        """更新数据"""
        updates = ', '.join([f"{k}=%s" for k in data])
        sql = f"UPDATE {table} SET {updates} WHERE {where}"
        params = list(data.values())  # 只组合data字典的值作为参数
        return self.execute(sql, params)

    def delete(self, table, where):
        """删除数据"""
        # 假设where是形如 "key1=value1 AND key2=value2" 的字符串
        condition = where  # 直接使用传入的where字符串
        sql = f"DELETE FROM {table} WHERE {condition}"
        return self.execute(sql, [])  # 不需要传递参数列表，因为条件已经包含在SQL中

    def query(self, sql, params=None):
        """查询数据"""
        return self.execute(sql, params)

# 使用示例 (仅在测试中使用)
if __name__ == "__main__":
    # 初始化MySQLHelper对象并连接数据库
    db = MySQLHelper('10.200.32.179', 'root', '111111', 'b23015121')
    db.connect()
    print(db.execute("SELECT * FROM QuestionAnswer"))
    db.insert("QuestionAnswer",{"image_path":"path/to/image.jpg","question":"What is this?","answer":"This is an example."})
    db.update("QuestionAnswer",{"question":"Update question.","answer":"新答案"},"id=4")
    db.delete("QuestionAnswer","id=100")
    # 关闭数据库连接
    db.close()